import consultasBD
import urllib
import urllib2
import re
import Mensaje_procesando

def addChange(sql):
    tipo = "I"
    if sql.upper().startswith("UPDATE"):
        tipo = "M"
    if sql.upper().startswith("DELETE"):
        tipo = "D"
    
    if tipo == "I":
        patter = "^INSERT INTO ([a-zA-Z0-9_]*).*"        
    elif tipo == "M":
        patter = "^UPDATE ([a-zA-Z0-9_]*).*"
    elif tipo == "D":
        tipo = "I"
        patter = "^DELETE FROM ([a-zA-Z0-9_]*).*"        
        
    p = re.compile(patter)
    tabla = p.findall(sql.upper())[0].lower()

    s = Syncronizator(tablaSQL=tabla)    
    s.addChange(sql, tipo)

def syncServerTables(parent, tablas):
    try:     
        consultasBD.begin()
        consultasBD.beginRemote()
                 
        dlg = Mensaje_procesando.Mensaje(parent)
        dlg.Show()
        
        s = Syncronizator(tablas=tablas)
        s.syncServer()
        
        consultasBD.commitRemote()
        consultasBD.commit()
        dlg.Close()
        
    except Exception, e:
        print e.__str__()
        dlg.Close()
        consultasBD.rollbackRemote()
        consultasBD.rollback()
        raise SyncError()

def syncServer(parent):    
    tablas = ["productos", "pedidos", "stock", "cliente", "producto_talle_color" ,"transferencias", "transferenciastieneproductos", "discrepancias", "grupo", "colores","cambiosprecios"]
    try:  
        consultasBD.begin()
        consultasBD.beginRemote()
        dlg = Mensaje_procesando.Mensaje(parent)
        dlg.Show()        
 
        s = Syncronizator(tablas=tablas)
        s.syncServer()  
            
        consultasBD.commitRemote()
        consultasBD.commit()
        dlg.Close()
        
    except Exception, e:
        print e.__str__()
        dlg.Close()
        consultasBD.rollbackRemote()
        consultasBD.rollback()
        raise SyncError()
    
class SyncError(Exception):
    pass

class Syncronizator:
    def __init__(self, tablaSQL="cliente", tablas=[], url_server='http://marianogalan.com.ar/erpsussan/syncTabla.php'):
        self.tablaSQL = tablaSQL
        self.sucursal = self._sucursal()
        self.url_server = url_server
        self.tablas = tablas
        self.tablas_in = ", ".join(["'%s'" % x for x in self.tablas])
        
    def _sucursal(self):
        sql = "SELECT * FROM sucursales WHERE activa = 1"
        resultado = consultasBD.ordenCrudaResultDict(sql)
        return resultado['idsucursal']

    def _verify_numero_version_table(self):
        sql = "SELECT * FROM version_tabla "
        result = consultasBD.ordenCrudaResultDict(sql)
        if len(result) == 0:
            sql = "INSERT INTO version_tabla (version) VALUES('%s')" % (0)
            consultasBD.executaSinCommit(None, sql)
            
    def addChange(self, sql, tipo):
        import time
        sql = str(sql)
        sql = sql.replace("'", "\\'")
        sql = sql.replace("`", "\\'")
        
        if self.tablaSQL == "stock":
            sql_insert_cambios = "INSERT INTO cambios (tabla, cambio, hora, tipo) VALUES('%s', '%s', SYSDATE(), '%s')" % ("stock_back", sql, tipo)
            ultimoid = consultasBD.executaSinCommit(None, sql_insert_cambios)
           
        sql_insert_cambios = "INSERT INTO cambios (tabla, cambio, hora, tipo) VALUES('%s', '%s', SYSDATE(), '%s')" % (self.tablaSQL, sql, tipo)
        ultimoid = consultasBD.executaSinCommit(None, sql_insert_cambios)
        
    def __delChangeList(self):
        sql = "DELETE FROM cambios WHERE tabla in (%s)" % self.tablas_in
        consultasBD.executaSinCommit(None, sql)

    def __getVersionClient(self):        
        sql_version = "SELECT COALESCE(max(version), 0) as version FROM version_tabla"
        return consultasBD.ordenCrudaResultDict(sql_version)['version']

    def __updateVersion(self, version):
        self._verify_numero_version_table()
        consultasBD.executaSinCommit(None, "UPDATE version_tabla SET version = '%s'" % version)
  
    def __getChangesClient(self):
        sql = "SELECT tipo, hora, cambio, tabla FROM cambios WHERE tabla in (%s)" % self.tablas_in
        print sql
        cambios = consultasBD.ordenCrudaDict(sql)
        return cambios
    
    def __updateVersionServer(self, version):
        sql = "UPDATE version_sucursales SET version = '%s' WHERE sucursal = '-1'" % str(version)
        consultasBD.executaSinCommitRemote(None, sql) 
    
    def __sendChanges(self, cambios):
        version = self.__getVersionServer()
        
        nueva_version = False
        for cam in cambios:
            nueva_version = True            
            
            tipo   = cam['tipo']
            hora   = cam['hora']
            cambio = cam['cambio']
            tabla  = cam['tabla']
            sucursal = self.sucursal
            
            sql = '''INSERT INTO cambios_servidor (tabla, cambio, version, sucursal, tipo, fecha) VALUES ("%s", "%s", "%s", "%s", "%s", "%s")''' % (tabla, cambio, version + 1, sucursal, tipo, hora)            
            consultasBD.executaSinCommitRemote(None, sql)            
        
        if nueva_version:    
            print "VERSION SERVER"    
            self.__updateVersionServer(version + 1)
            
    def __getChangesServer(self, version):
        sql = "SELECT cambio FROM cambios_servidor WHERE version > '%s' and (sucursal <> '%s' or tipo = 'M') ORDER BY version ASC" % (version, self.sucursal)
        return consultasBD.ordenCrudaDictRemote(sql)
    
    def __integrateChanges(self, cambios_servidor):
        if cambios_servidor:
            for cs in cambios_servidor:
                print cs['cambio']
                consultasBD.executaSinCommit(None, cs['cambio'])     
                
    def __getVersionServer(self):
        sql = "SELECT COALESCE( max( version ) , 0 ) as version FROM version_sucursales WHERE sucursal = '-1'"
        return consultasBD.ordenCrudaDictRemote(sql)[0]['version']

    
    def _verify_sucural_version(self):
        sql = "SELECT * FROM version_sucursales WHERE sucursal = '%s'" % self.sucursal
        result = consultasBD.ordenCrudaResultDictRemote(sql)
        if len(result) == 0:
            sql = "INSERT INTO version_sucursales (version, sucursal) VALUES('%s', '%s')" % (0, self.sucursal)
            consultasBD.executaSinCommitRemote(None, sql)
    
    def __updateVersionSucursal(self, version):
        self._verify_sucural_version()
        sql = "UPDATE version_sucursales SET version = '%s' WHERE sucursal = '%s'" % (str(version), self.sucursal)
        consultasBD.executaSinCommitRemote(None, sql)        
            
    def syncServer(self):      
        cambios = self.__getChangesClient()
        version = self.__getVersionClient()    
        self.__sendChanges(cambios)
        cambios_servidor = self.__getChangesServer(version)
        version_servidor = self.__getVersionServer()
        self.__updateVersionSucursal(version_servidor)
        self.__integrateChanges(cambios_servidor)                     
        self.__updateVersion(version_servidor)                
        self.__delChangeList()            